create database TarsDeliverySystemDemo
go
use TarsDeliverySystemDemo
go
----------------1.CREATE TABLE -------------------
create table [SERVICES]
(
   SERVICE_ID int identity(1,1) primary key not null,
   SERVICE_CODE varchar(100),
   [SERVICE_NAME] varchar(100),
   [DESCRIPTION] varchar(1000)
)
create table NEWS
(
NEW_ID int identity(1,1) primary key not null,
TITLE varchar(500),
[SUBJECT] varchar(500),
[BODY] ntext,
[CREATE_DATE] datetime,
[STATUS] bit
)
create table FEEDBACK
(
FEED_ID int identity(1,1) primary key not null,
FIRST_NAME varchar(500),
LAST_NAME varchar(500),
EMAIL varchar(300),
BODY ntext
)
CREATE TABLE COMPANIES
(
	[COMPANY_ID] int IDENTITY(1,1) primary key,
	[COMPANY_NAME] varchar(300),
	[COMPANY_CODE] varchar(300),
	[COMPANY_PHONE] varchar(300) NOT NULL,
	[COMPANY_ADDRESS] varchar(300) NOT NULL,
	[COMPANY_EMAIL] varchar(300) NULL,
	[DESCRIPTION] varchar(1000)
)
CREATE TABLE CITIES
(
	CITY_ID int IDENTITY(1,1)primary key,
	[CITY_CODE] varchar(300) ,
	[CITY_NAME] varchar(300),
	[DESCRIPTION] varchar(300)
)
CREATE TABLE BRANCHES
(
	BRANCH_ID int IDENTITY(1,1)primary key NOT NULL,
	COMPANY_ID int constraint BS_CI foreign key references COMPANIES(COMPANY_ID) NOT NULL,
	CITY_ID int constraint BS_CITYID foreign key references CITIES(CITY_ID) NOT NULL,
	[BRANCH_CODE] varchar(300) NOT NULL,
	[BRANCH_NAME] varchar(400) NOT NULL,
	[BRANCH_PHONE] varchar(100) NOT NULL,
	[ADDRESS] varchar(300) NOT NULL,
	[DESCRIPTION] varchar(1000) NULL
)
CREATE TABLE DELIVERY_FEE_POLICIES
(
	FEE_POLICY_ID int IDENTITY(1,1)primary key NOT NULL,
	SERVICE_ID int constraint DB_SERVICEID foreign key references [SERVICES](SERVICE_ID) NOT NULL,
	DISTANCE_FROM float NOT NULL,
	DISTANCE_TO float NOT NULL,
	FEE_PER_KM float NOT NULL,
	WEIGHT_FROM float NOT NULL,
	WEIGHT_TO float NOT NULL,
	FEE_PER_KG float NULL
	)
	CREATE TABLE DISTANCES
	(
	DISTANCE_ID int IDENTITY(1,1) primary key NOT NULL,
	BRANCH_ID_FROM int constraint DS_BF foreign key references BRANCHES(BRANCH_ID) NULL,
	BRANCH_ID_TO int NULL,
	KM float NULL,
	)
	CREATE TABLE ACCOUNTS(
	[ACCOUNT_ID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	BRANCH_ID int constraint AC_BF foreign key references BRANCHES(BRANCH_ID) NOT NULL,
	[USER_NAME] varchar(300) NOT NULL,
	[PASSWORD] varchar(500) NOT NULL,
	[ROLE] int NOT NULL,
	[CREATE_DATE] datetime NULL,
	[DESCRIPTION] varchar(1000) NULL,
	[PIN_CODE] varchar(10)NOT NULL,
	[BIRTH_DATE] varchar(10) NULL,
	[FIRST_NAME] varchar(300) NULL,
	[LAST_NAME] varchar(300) NULL,
	[PHONE_NUMBER] varchar(300) NULL,
	[EMAIL] varchar(300) NULL,
	[ADDRESS] varchar(300) NULL,
	CITY_ID int constraint AC_CI foreign key references CITIES(CITY_ID) NULL,
	[PICTURE] varchar(200) NULL,
	[POSITION] varchar(300) NULL,
	[STATUS] int NULL,
	[ACTIVE] int NULL
	)	
	CREATE TABLE ORDERS
	(
	[ORDER_ID] int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[ORDER_NUMBER] varchar(300) NOT NULL,
	[ORDER_DATE] varchar(10) Not null,
	SERVICE_ID int constraint OR_SI foreign key references [SERVICES](SERVICE_ID) NOT NULL,
	[ACCOUNT_ID] int constraint OR_AI foreign key references ACCOUNTS([ACCOUNT_ID]) NOT NULL,
	[IS_INTERNAL_DELIVERY] int not NULL,
	[BRANCH_ID_FROM] int constraint OR_BF foreign key references BRANCHES(BRANCH_ID) NULL,
	[BRANCH_ID_TO] int constraint OR_BT foreign key references BRANCHES(BRANCH_ID) NULL,
	[RECEIVE_DATE] varchar(10) NULL,
	[STATUS] varchar(200) NULL,
	[SENDER_NAME] varchar(300) NULL,
	[SENDER_ADDRESS] varchar(300) NULL,
	[SENDER_CITY] int NULL,
	[SENDER_PHONE] varchar(100) NULL,
	[RECEIVER_NAME] varchar(300) NULL,
	[RECEIVER_ADDRESS] varchar(300) NULL,
	[RECEIVER_CITY] int NULL,
	[RECEIVER_PHONE] varchar(100) NULL,
	[MAIL_CODE] varchar(300) NOT NULL,
	[WEIGHT] float NOT NULL,
	[DESCRIPTION] varchar(500) NULL,
	[TOTAL_DELIVERY_FEE] float NULL,
	[TOTAL_PAID] float NULL
	)
	go
	------------------ 2.STORE PROCEDURE------------------	
	                ----A.Non Parameter----
	CREATE PROCEDURE ViewAllService
	AS 
	SELECT	*	FROM [SERVICES]
	ORDER BY SERVICE_ID DESC
	go
	CREATE PROCEDURE ViewAllNEWS
	AS 
	SELECT	*	FROM NEWS
	ORDER BY NEW_ID DESC
	go
	CREATE PROCEDURE ViewCITY_ID_CITY_NAME
	AS 
	select CITY_ID,CITY_NAME from CITIES
	ORDER BY CITY_ID DESC
	go
	CREATE PROCEDURE ViewAllBranch
	AS 
	select BRANCH_ID,BRANCH_NAME from BRANCHES
	ORDER BY BRANCH_ID DESC
	go
	CREATE PROCEDURE ViewAllAccount
	AS 
	select * from ACCOUNTS
	ORDER BY ACCOUNT_ID DESC
	go
	CREATE PROCEDURE ViewAllCompany
	AS 
	select * from COMPANIES
	ORDER BY COMPANY_ID DESC
	go
	CREATE PROCEDURE ViewAllCity
	AS 
	select * from CITIES
	ORDER BY CITY_ID DESC
	go	
	CREATE PROCEDURE ViewAllBranch1
	AS 
	select * from BRANCHES
	ORDER BY BRANCH_ID DESC
	go
	CREATE PROCEDURE ViewAllDistance
	AS 
	select * from DISTANCES
	ORDER BY DISTANCE_ID DESC
	go
	CREATE PROCEDURE ViewAllFee
	AS 
	select * from DELIVERY_FEE_POLICIES
	ORDER BY FEE_POLICY_ID DESC
	go
	CREATE PROCEDURE ViewSer
	AS 
	select SERVICE_CODE,[SERVICE_NAME],SERVICE_ID from [SERVICES]
	ORDER BY SERVICE_ID DESC
	go
	CREATE PROCEDURE ViewFeed
	AS 
	select * from FEEDBACK
	ORDER BY FEED_ID DESC
	go
	CREATE PROCEDURE ViewORDERS
	AS 
	select * from ORDERS
	ORDER BY ORDER_ID DESC
	go
	                ----B.Parameter----
	                ----B1. Select------
	create procedure SearchMail_Code
      @v_CCode varchar(50)
    AS
    select * from ORDERS where MAIL_CODE=@v_CCode
	go
	create procedure lOADDETAILoDER
      @v_CCode int
    AS
    select * from ORDERS where ORDER_ID=@v_CCode
	go
	create procedure SearchCITY_ID
      @v_CCode int
    AS
    select * from BRANCHES where CITY_ID=@v_CCode
	go
	create procedure SearchSERVICES_IDinDELIVERY_FEE_POLICIES
      @v_CCode int
    AS
    select * from DELIVERY_FEE_POLICIES where SERVICE_ID=@v_CCode
	go
	create procedure SearchSERVICES_ID
      @v_CCode int
    AS
    select * from [SERVICES] where SERVICE_ID=@v_CCode
	go
	create procedure CheckLogin
      @User varchar(300),@Pass varchar(500)
    AS
    select * from ACCOUNTS where [USER_NAME]=@User and [PASSWORD]=@Pass
	go
	create procedure LoadNews
      @v_CCode int
    AS
    select * from News where NEW_ID=@v_CCode
	go
	create procedure SearchBranchID
      @v_CCode int
    AS
    select * from BRANCHES where BRANCH_ID=@v_CCode
    go	
	create procedure getAccount
      @v_CCode varchar(30)
    AS
    select * from ACCOUNTS where [USER_NAME]=@v_CCode
	go	
	create procedure CheckPincode
      @v_CCode varchar(10)
    AS
    select * from ACCOUNTS where PIN_CODE=@v_CCode
	go
	create procedure SearchAccount
      @v_CCode varchar(30)
    AS    
    select * from ACCOUNTS where [USER_NAME] LIKE '%'+@v_CCode+'%' or FIRST_NAME LIKE '%'+@v_CCode+'%'
	go
	create procedure SearchAccountID
      @v_CCode int
    AS    
    select * from ACCOUNTS where ACCOUNT_ID = @v_CCode
	go	
	create procedure SearchCompany
      @v_CCode int
    AS    
    select * from COMPANIES where COMPANY_ID = @v_CCode
	go
	create procedure SearchCity
      @v_CCode int
    AS    
    select * from CITIES where CITY_ID = @v_CCode
	go
	create procedure SearchDistance
      @v_CCode int
    AS    
    select * from DISTANCES where DISTANCE_ID = @v_CCode
	go
	create procedure SearchFeePolicy
      @v_CCode int
    AS    
    select * from DELIVERY_FEE_POLICIES where FEE_POLICY_ID = @v_CCode
	go		
		create procedure SearchEmployee_ID
      @v_CCode int
    AS    
    select * from ACCOUNTS where BRANCH_ID = @v_CCode
	go	
	create procedure SearchCityName
      @v_CCode varchar(30)
    AS    
    select * from CITIES where CITY_NAME LIKE '%'+@v_CCode+'%' 
	go	
	create procedure SearchBranchName
     @val int
    AS    
    select * from BRANCHES where  CITY_ID = @val
	go

	create procedure SearchOrder
      @v_CCode varchar(30)
    AS    
    select * from ORDERS where MAIL_CODE LIKE '%'+@v_CCode+'%' 

	go	
	create procedure SearchOrderList
      @v_CCode int
    AS    
    select * from ORDERS where ACCOUNT_ID  = @v_CCode
	go					
	create procedure CalculatorA
     @ValA int,@ValB int
    AS    
    select * from DISTANCES where BRANCH_ID_FROM = @ValA and BRANCH_ID_TO = @ValB 
	go					
    create procedure CalculatorB
     @ValA int,@ValB int
    AS    
    select * from DELIVERY_FEE_POLICIES where (DISTANCE_FROM <= @ValA and @ValA <= DISTANCE_TO) and SERVICE_ID = @ValB 
	go	
	create procedure SearchOrderPro
      @A int,@B int, @C varchar(10),@D int, @E varchar(20),@F varchar(20),@R int
    AS    
    select * from ORDERS where (BRANCH_ID_FROM =  @A and  BRANCH_ID_TO = @B) or (ORDER_DATE = @C or SERVICE_ID = @D or STATUS=@E or MAIL_CODE=@F or IS_INTERNAL_DELIVERY = @R)
	go	
	create procedure SearchFeePo
     @ValA float,@ValB float
    AS    
    select * from DELIVERY_FEE_POLICIES where FEE_PER_KM = @ValA or FEE_PER_KG = @ValB
	go	
	create procedure SearchDis
     @ValA float,@ValB float
    AS    
    select * from DISTANCES where KM <= @ValA and KM <= @ValB 
	go	
	create procedure SearchNews
      @v_CCode varchar(30)
    AS    
    select * from NEWS where TITLE LIKE '%'+@v_CCode+'%' 
	go					
	--------------------B.2 Delete---------------------------------
	create procedure DeleteAccount
      @v_CCode int
    AS    
    DELETE FROM ACCOUNTS WHERE ACCOUNT_ID =@v_CCode
	go
	create procedure DeleteNew
      @v_CCode int
    AS    
    DELETE FROM NEWS WHERE NEW_ID =@v_CCode
	go
	create procedure DeleteBran
      @v_CCode int
    AS    
    DELETE FROM BRANCHES WHERE BRANCH_ID =@v_CCode
    go
    create procedure DeleteOrder
      @v_CCode int
    AS    
    DELETE FROM ORDERS WHERE ORDER_ID =@v_CCode
    go
    create procedure DeleteFeed
      @v_CCode int
    AS    
    DELETE FROM FEEDBACK WHERE FEED_ID =@v_CCode
	go
	create procedure DeleteCity
      @v_CCode int
    AS    
    DELETE FROM CITIES WHERE CITY_ID =@v_CCode
	go
	create procedure DeleteCom
      @v_CCode int
    AS    
    DELETE FROM COMPANIES WHERE COMPANY_ID =@v_CCode
	go
	create procedure DeleteDis
      @v_CCode int
    AS    
    DELETE FROM DISTANCES WHERE DISTANCE_ID =@v_CCode
	go
	create procedure DeleteDisFee
      @v_CCode int
    AS    
    DELETE FROM DELIVERY_FEE_POLICIES WHERE FEE_POLICY_ID =@v_CCode	
	go
	create procedure DeleteService
      @v_CCode int
    AS    
    DELETE FROM [SERVICES] WHERE SERVICE_ID =@v_CCode	
	go
	create procedure ChangePasswordEM
      @v_CCode int,@Pass varchar(400)
    AS    
    Update ACCOUNTS set [PASSWORD]=@Pass WHERE ACCOUNT_ID=@v_CCode	
	go
    create procedure DeleteOrders
      @v_CCode int
    AS    
    DELETE FROM ORDERS WHERE ORDER_ID =@v_CCode	
	go
	--------------------B.3 Insert--------------------------------
	create procedure InsertCompany
      @A varchar(300),@B varchar(300),@C varchar(300),@D varchar(300),@E varchar(300),@F varchar(300)
    AS    
    Insert into COMPANIES values(@A,@B,@C,@D,@E,@F)
	go
	create procedure InsertNewAccount
    @active int,@status int,@pos varchar(40),@city int,@Pin varchar(12),@Des varchar(300),@create datetime,@Role int,@Pass varchar(500),@Bran int,@v_CCode varchar(30),@First varchar(30), @LastName varchar(500), @Pic varchar(500),@Birth varchar(10), @Phone varchar(50),@Email varchar(60),@Address varchar(300)
    AS
    insert into ACCOUNTS values(@Bran,@v_CCode,@Pass,@Role,@create,@Des,@Pin,@Birth,@First,
	@LastName,@Phone,@Email,@Address,@city,@Pic,@pos,@status,@active)
	go
	create procedure InsertCity
      @A varchar(300),@B varchar(300),@C varchar(300)
    AS    
    Insert into CITIES values(@A,@B,@C)
	go
	create procedure InsertBranch
      @A int,@B int,@C varchar(300),@D varchar(400),@E varchar(100),@F varchar(300),@G varchar(1000)
    AS    
    Insert into BRANCHES values(@A,@B,@C,@D,@E,@F,@G)
	go
	create procedure InsertDistance
      @A int,@B int,@C float
    AS    
    Insert into DISTANCES values(@A,@B,@C)
	go
	create procedure InsertService
      @A varchar(100),@B varchar(100),@C varchar(100)
    AS    
    Insert into [SERVICES] values(@A,@B,@C)
	go
	create procedure InsertNews
      @A varchar(500),@B varchar(500),@C ntext, @D datetime,@E bit
    AS    
    Insert into NEWS values(@A,@B,@C,@D,@E)
	go
	create procedure InsertFeedback
      @A varchar(500),@B varchar(500),@C varchar(300), @D ntext
    AS    
    Insert into FEEDBACK values(@A,@B,@C,@D)
	go
	create procedure InsertOrderA
      @ORDER_NUMBER varchar(300),@ORDER_DATE varchar(10),@SERVICE_ID int, @ACCOUNT_ID int,@IS_INTERNAL_DELIVERY int,@BRANCH_ID_FROM int,@BRANCH_ID_TO int, @STATUS varchar(200),@SENDER_NAME varchar(100),@SENDER_ADDRESS varchar(200),@SENDER_CITY int,@SENDER_PHONE varchar(300),@RECEIVER_NAME varchar(300),@RECEIVER_ADDRESS varchar(100),@RECEIVER_CITY int,@RECEIVER_PHONE varchar(300),@MAIL_CODE varchar(300),@WEIGHT float ,@DESCRIPTION varchar(300),@TOTAL_DELIVERY_FEE float,@TOTAL_PAID float
    AS 
    Insert into ORDERS (ORDER_NUMBER, ORDER_DATE, SERVICE_ID, ACCOUNT_ID,IS_INTERNAL_DELIVERY,BRANCH_ID_FROM,BRANCH_ID_TO,STATUS,SENDER_NAME,SENDER_ADDRESS,SENDER_CITY,SENDER_PHONE,RECEIVER_NAME,RECEIVER_ADDRESS,RECEIVER_CITY,RECEIVER_PHONE,MAIL_CODE,WEIGHT,DESCRIPTION,TOTAL_DELIVERY_FEE,TOTAL_PAID)  values(@ORDER_NUMBER,@ORDER_DATE,@SERVICE_ID,@ACCOUNT_ID,@IS_INTERNAL_DELIVERY,@BRANCH_ID_FROM,@BRANCH_ID_TO,@STATUS,@SENDER_NAME,@SENDER_ADDRESS,@SENDER_CITY,@SENDER_PHONE,@RECEIVER_NAME,@RECEIVER_ADDRESS,@RECEIVER_CITY,@RECEIVER_PHONE,@MAIL_CODE,@WEIGHT,@DESCRIPTION,@TOTAL_DELIVERY_FEE,@TOTAL_PAID)

	go
	create procedure InsertOrderB
      @ORDER_NUMBER varchar(300),@ORDER_DATE varchar(10),@SERVICE_ID int, @ACCOUNT_ID int,@IS_INTERNAL_DELIVERY int,@BRANCH_ID_FROM int,@BRANCH_ID_TO int,@STATUS varchar(200),@MAIL_CODE varchar(300),@WEIGHT float,@DESCRIPTION varchar(300),@TOTAL_DELIVERY_FEE float,@TOTAL_PAID varchar(500)
    AS    
    Insert into ORDERS (ORDER_NUMBER, ORDER_DATE, SERVICE_ID, ACCOUNT_ID,IS_INTERNAL_DELIVERY,BRANCH_ID_FROM,BRANCH_ID_TO,STATUS,MAIL_CODE,WEIGHT,DESCRIPTION,TOTAL_DELIVERY_FEE,TOTAL_PAID)  values(@ORDER_NUMBER,@ORDER_DATE,@SERVICE_ID,@ACCOUNT_ID,@IS_INTERNAL_DELIVERY,@BRANCH_ID_FROM,@BRANCH_ID_TO,@STATUS,@MAIL_CODE,@WEIGHT,@DESCRIPTION,@TOTAL_DELIVERY_FEE,@TOTAL_PAID)
	go
	create procedure InsertFeePolicy
      @A int,@B float,@C float,@D float,@E float,@F float,@G float
    AS    
    Insert into DELIVERY_FEE_POLICIES values(@A,@B,@C,@D,@E,@F,@G)
	go
	--------------------B.4 Check--------------------------
	create procedure CheckCompany
      @v_CCode int
    AS    
    select A.COMPANY_ID,A.COMPANY_ADDRESS,A.COMPANY_CODE, B.COMPANY_ID,B.BRANCH_ID,B.BRANCH_NAME from COMPANIES A inner join BRANCHES B on (A.COMPANY_ID = B.COMPANY_ID)   where A.COMPANY_ID = @v_CCode 	
	go
	create procedure CheckAccount
      @v_CCode int
    AS    
    select  A.ACCOUNT_ID , A.ACTIVE, B.ACCOUNT_ID ,B.DESCRIPTION from ACCOUNTS A inner join ORDERS B on (A.ACCOUNT_ID = B.ACCOUNT_ID)   where A.ACCOUNT_ID = @v_CCode 	
	go
	create procedure CheckCITY1
      @v_CCode int
    AS    
    select A.CITY_ID , A.CITY_NAME ,B.CITY_ID ,B.ACTIVE  from CITIES A inner join ACCOUNTS B on (A.CITY_ID = B.CITY_ID)   where A.CITY_ID = @v_CCode 	
	go
	create procedure CheckCITY2
      @v_CCode int
    AS    
    select A.CITY_ID ,A.DESCRIPTION ,B.CITY_ID , B.ADDRESS  from CITIES A inner join BRANCHES B on (A.CITY_ID = B.CITY_ID)   where A.CITY_ID = @v_CCode 	
	go
	create procedure CheckBRANCHES1
      @v_CCode int
    AS    
    select A.BRANCH_ID ,A.ADDRESS , B.BRANCH_ID_FROM ,B.KM from BRANCHES A inner join DISTANCES B on (A.BRANCH_ID = B.BRANCH_ID_FROM)   where A.BRANCH_ID = @v_CCode 	
	go
	create procedure CheckBRANCHES2
      @v_CCode int
    AS    
    select A.BRANCH_ID ,A.BRANCH_NAME ,B.BRANCH_ID_FROM ,B.MAIL_CODE from BRANCHES A inner join ORDERS B on (A.BRANCH_ID = B.BRANCH_ID_FROM)   where A.BRANCH_ID = @v_CCode 	
	go
	create procedure CheckSERVICE1
      @v_CCode int
    AS    
    select A.SERVICE_ID ,A.DESCRIPTION , B.SERVICE_ID , B.FEE_POLICY_ID from SERVICES A inner join DELIVERY_FEE_POLICIES B on (A.SERVICE_ID = B.SERVICE_ID)   where A.SERVICE_ID = @v_CCode 	
	go
	create procedure CheckSERVICE2
      @v_CCode int
    AS    
    select A.SERVICE_ID ,A.DESCRIPTION ,B.SERVICE_ID , B.ORDER_DATE  from SERVICES A inner join ORDERS B on (A.SERVICE_ID = B.SERVICE_ID)   where A.SERVICE_ID = @v_CCode 	
	go
    
	--------------------B.5 Update---------------------------------
	create procedure ChangePasswordAD
      @v_CCode varchar(30), @OldPass varchar(500), @Pass varchar(500)
    AS
    update ACCOUNTS set [PASSWORD] = @Pass where [USER_NAME] = @v_CCode and [PASSWORD] = @OldPass
	go
	create procedure UpdateProfile
      @v_CCode varchar(30),@First varchar(30), @LastName varchar(500), @Pic varchar(500),@Birth varchar(10), @Phone varchar(50),@Email varchar(60),@Address varchar(300)
    AS
    update ACCOUNTS set FIRST_NAME = @First, LAST_NAME= @LastName,PICTURE = @Pic,BIRTH_DATE = @Birth,PHONE_NUMBER=@Phone,EMAIL=@Email,[ADDRESS] = @Address   where [USER_NAME] = @v_CCode
	go	
	create procedure UpdateCompany
    @A varchar(300), @B varchar(300), @C varchar(300),@D varchar(300),@E varchar(300),@F varchar(300),@V int
    AS
    update COMPANIES set COMPANY_CODE = @A, COMPANY_NAME= @B,COMPANY_EMAIL = @C,COMPANY_ADDRESS = @D,COMPANY_PHONE=@E,[DESCRIPTION] = @F   where COMPANY_ID = @V
	go	
	create procedure UpdateCity
    @A varchar(300), @B varchar(300), @C varchar(300),@V int
    AS
    update CITIES set CITY_CODE = @A, CITY_NAME= @B,DESCRIPTION = @C where CITY_ID = @V
	go
	create procedure UpdateBranch
     @A int,@B int,@C varchar(300),@D varchar(400),@E varchar(100),@F varchar(300),@G varchar(1000),@Z int
    AS
    update BRANCHES set COMPANY_ID = @A, CITY_ID= @B,[BRANCH_CODE] = @C,[BRANCH_NAME]= @D,[BRANCH_PHONE]= @E,[ADDRESS]= @F,[DESCRIPTION]= @G where BRANCH_ID = @Z
	go
	create procedure UpdateDistance
     @A int,@B int,@C float,@Z int
    AS
    update DISTANCES set BRANCH_ID_FROM = @A, BRANCH_ID_TO = @B,KM = @C where DISTANCE_ID = @Z
	go
	create procedure UpdateService
     @A varchar(100),@B varchar(100),@C varchar(100),@Z int
    AS
    update [SERVICES] set SERVICE_CODE = @A, [SERVICE_NAME] = @B,[DESCRIPTION] = @C where SERVICE_ID = @Z
	go
	create procedure UpdateFeePolicy
     @A int,@B float,@C float,@D float,@E float,@F float,@G float,@Z int
    AS
    update DELIVERY_FEE_POLICIES set SERVICE_ID = @A, DISTANCE_FROM = @B,DISTANCE_TO = @C,FEE_PER_KM= @D,WEIGHT_FROM= @E,WEIGHT_TO= @F,FEE_PER_KG= @G where FEE_POLICY_ID = @Z
	go
	create procedure UpdateNews
     @A varchar(500),@B varchar(500),@C ntext, @D datetime,@E bit,@Z int
    AS
    update NEWS set TITLE = @A, [SUBJECT] = @B,[BODY] = @C,[CREATE_DATE]= @D,[STATUS]= @E where NEW_ID = @Z
	go
	create procedure UpdateFeedback
     @A varchar(500),@B varchar(500),@C varchar(300), @D ntext,@Z int
    AS
    update FEEDBACK set FIRST_NAME = @A, LAST_NAME = @B,EMAIL = @C,BODY= @D where FEED_ID = @Z
	go
	create procedure UpdateOrderA
      @ORDER_NUMBER varchar(300),@ORDER_DATE varchar(10),@SERVICE_ID int, @ACCOUNT_ID int,@IS_INTERNAL_DELIVERY int,@BRANCH_ID_FROM int,@BRANCH_ID_TO int,@RECEIVE_DATE varchar(10), @STATUS varchar(200),@SENDER_NAME varchar(100),@SENDER_ADDRESS varchar(200),@SENDER_CITY int,@SENDER_PHONE varchar(300),@RECEIVER_NAME varchar(300),@RECEIVER_ADDRESS varchar(100),@RECEIVER_CITY int,@RECEIVER_PHONE varchar(300),@MAIL_CODE varchar(300),@WEIGHT float,@DESCRIPTION varchar(300),@TOTAL_DELIVERY_FEE float,@TOTAL_PAID float,@Z int
    AS
    update ORDERS set [ORDER_NUMBER] = @ORDER_NUMBER, [ORDER_DATE] = @ORDER_DATE,SERVICE_ID = @SERVICE_ID,[ACCOUNT_ID]= @ACCOUNT_ID,[IS_INTERNAL_DELIVERY]= @IS_INTERNAL_DELIVERY,[BRANCH_ID_FROM]= @BRANCH_ID_FROM,[BRANCH_ID_TO]= @BRANCH_ID_TO,[RECEIVE_DATE]= @RECEIVE_DATE,[STATUS]= @STATUS,
    [SENDER_NAME]= @RECEIVER_NAME,[SENDER_ADDRESS]= @SENDER_ADDRESS,[SENDER_CITY]= @SENDER_CITY,[SENDER_PHONE]= @RECEIVER_PHONE,[RECEIVER_NAME]= @RECEIVER_NAME,[RECEIVER_ADDRESS]= @RECEIVER_ADDRESS,[RECEIVER_CITY]= @RECEIVER_CITY,[RECEIVER_PHONE]= @RECEIVER_PHONE,[MAIL_CODE]= @MAIL_CODE,[WEIGHT]= @WEIGHT,[DESCRIPTION]= @DESCRIPTION,[TOTAL_DELIVERY_FEE]= @TOTAL_DELIVERY_FEE,[TOTAL_PAID]= @TOTAL_PAID where ORDER_ID = @Z
	go
	create procedure UpdateOrderB
      @ORDER_NUMBER varchar(300),@ORDER_DATE varchar(10),@SERVICE_ID int, @ACCOUNT_ID int,@IS_INTERNAL_DELIVERY int,@BRANCH_ID_FROM int,@BRANCH_ID_TO int,@RECEIVE_DATE varchar(10),@STATUS varchar(200),@MAIL_CODE varchar(300),@WEIGHT float,@DESCRIPTION varchar(300),@TOTAL_DELIVERY_FEE float,@TOTAL_PAID float,@Z int
    AS
    update ORDERS set [ORDER_NUMBER] = @ORDER_NUMBER, [ORDER_DATE] = @ORDER_DATE,SERVICE_ID = @SERVICE_ID,[ACCOUNT_ID]= @ACCOUNT_ID,[IS_INTERNAL_DELIVERY]= @IS_INTERNAL_DELIVERY,[BRANCH_ID_FROM]= @BRANCH_ID_FROM,[BRANCH_ID_TO]= @BRANCH_ID_TO,[RECEIVE_DATE]= @RECEIVE_DATE,[STATUS]= @STATUS,[MAIL_CODE]= @MAIL_CODE,[WEIGHT]= @WEIGHT,[DESCRIPTION]= @DESCRIPTION,[TOTAL_DELIVERY_FEE]= @TOTAL_DELIVERY_FEE,[TOTAL_PAID]= @TOTAL_PAID where ORDER_ID = @Z
	go
	
		
	--------------------C.Get Total-------------------------------
	create procedure getCompany     
    AS
    select count ([COMPANY_NAME]) from COMPANIES
	go
	create procedure getCity    
    AS
    select count (CITY_NAME) from CITIES
	go
	create procedure getBRANCHES    
    AS
    select count (BRANCH_NAME) from BRANCHES
	go
	create procedure getACCOUNTS  
    AS
    select count([USER_NAME]) from ACCOUNTS
	go
	create procedure getSERVICES    
    AS
    select count ([SERVICE_NAME]) from [SERVICES]
	go
	create procedure getFEEDBACK    
    AS
    select count (FEED_ID) from FEEDBACK
	go
	create procedure getActive    
    AS
    select count (ACTIVE) from ACCOUNTS Where ACTIVE = 0
	go
	--------------------3.INSERT VALUES----------------------
	insert into [SERVICES] values('VPP','VPP DELIVERY','This is a service that allows people to send parcels to recipients,and recipients will pay for the cost of parcel post'),
	                           ('EXP','EXPRESS DELIVERY','This service allows people to send parcels quickly and ensures on-time service will be transported in the shortest possible time,possibly within a day.'),
	                            ('NOR','NORMAL DELIVERY','This service usually transfer mail allows the sender of the parcel or letter.    This service is offered to customers who do not require in terms of time,    with features such Normal Mail is the appropriate choice for customers.')
	INSERT INTO NEWS VALUES ('VIET NAM VO DICH WORLD CUP','VIET NAM DANG QUANG TAI GIAI DAU DANH GIA NHAT CHO CAP QUOC GIA','AAAAAAAA','2010-04-12 16:15:06.200',1),
	                        ('TRUNG QUOC SO HAI QUAN VIET NAM','VIET NAM DANG QUANG TAI GIAI DAU DANH GIA NHAT CHO CAP QUOC GIA','AAAAAAAA','2010-04-12 16:15:06.200',1),
	                        ('VIET NAM TRANH CHAP DAO HAI NAM','VIET NAM DANG QUANG TAI GIAI DAU DANH GIA NHAT CHO CAP QUOC GIA','AAAAAAAA','2010-04-12 16:15:06.200',1)
	insert into CITIES values('AG','AN GIANG','AN GIANG'),
	                         ('KG','KIEN GIANG','KIEN GIANG'),                       
	                         ('HCM','HO CHI MINH','HO CHI MINH'),
	                         ('NT','NINH THUAN','NINH THUAN'),
	                         ('KT','KON TUM','KON TUM')	                         
	insert into COMPANIES values('IcedTea','IT','+84975955380','VIET NAM','magiccry94@gmail.com','IcedTea Company'),
	                            ('DanhBui','DB','+84113114115','VIET NAM','DanhBui@gmail.com','DanhBui Company'),
	                            ('BacKinh','BK','+84113115114','VIET NAM','BACKINH@gmail.com','BacKinh company'),
	                            ('QuocLee','QL','+84113114115','VIET NAM','QuocLee0512@gmail.com','QuocLee Company'),
	                            ('DaiVe','DV','+84113115114','VIET NAM','DaiVe@gmail.com','DaiVe company')
	                         
	insert into BRANCHES values(1,1,'AG01','Chau Phu','0975955380','59/02 Binh Thuy','Has Established'),
	                            (1,1,'AG02','Chau Doc','0975955390','60/05 Dinh Tien Hoang','Has Established'),
	                            (2,2,'KG01','Ha Tien','0973933280','55/14 Hung Vuong','Has Established'),
	                            (2,2,'KG02','Rach Gia','0975855882','26/16 Ly Thuong Kiet','Has Established'),
	                            (3,3,'HCM01','DISTRICT 1','0973933280','595/145 Vo Thi Sau','Has Established'),
	                            (3,3,'HCM02','DISTRICT 12','0975868882','256/146 Tan Chanh Hiep','Has Established'),
	                            (4,4,'NT01','PHAN RANG','0973934526','595/145 30/4','Has Established'),
	                            (4,4,'NT02','PHUOC MINH','0962502452','256/146 LE LAI','Has Established'),
	                            (5,5,'KT01','KROONG','0973936576','595/145 QL675','Has Established'),
	                            (5,5,'KT02','KONRAKLAH','0978502452','256/146 Hai Ba Trung','Has Established')
	                           
	INSERT INTO ACCOUNTS VALUES(1,'ADMIN','21232F297A57A5A743894A0E4A801FC3',1,'2010-04-02 16:19:49.107','ADMIN','AD','21/05/1994','Nam','Ho Thanh','0975955380','namhtgc60226@fpt.edu.vn','An Giang',1,'~/Object One/Pic/Ava/ADMIN.jpg','Director',1,1),
	                            (2,'DanhBui','E10ADC3949BA59ABBE56E057F20F883E',0,'2010-04-02 16:19:49.107','USER','UR','30/10/1991','DANH','BUI DANG HONG','0975955380','DANHBDH@fpt.edu.vn','KIEN Giang',2,'~/Object One/Pic/Ava/DANH.jpg','EMPLOYEE',1,0),
	                            (3,'Backaka','E10ADC3949BA59ABBE56E057F20F883E',0,'2010-04-02 16:19:49.107','USER','UR','1993-05-21','BAC','DINH NGOC BAC','0969781201','BACDNGC60189@fpt.edu.vn','HO CHI MINH',2,'~/Object One/Pic/Ava/BAC.jpg','EMPLOYEE',1,1),
	                            (4,'QuocLee','E10ADC3949BA59ABBE56E057F20F883E',0,'2010-04-02 16:19:49.107','USER','UR','1993-12-05','QUOC','LE HUNG QUOC','01688391731','QUOCLHGC60212@fpt.edu.vn','NINH THUAN',2,'~/Object One/Pic/Ava/QUOC.jpg','EMPLOYEE',1,0),
	                            (5,'VeLibya','E10ADC3949BA59ABBE56E057F20F883E',0,'2010-04-02 16:19:49.107','USER','UR','1992-05-21','VE','DUONG DAI VE','01652060340','VEDDGC60133@fpt.edu.vn','KON TUM',2,'~/Object One/Pic/Ava/VE.jpg','EMPLOYEE',1,1)
	                           
    INSERT INTO DELIVERY_FEE_POLICIES VALUES (1,0,100,10,0,100,15),
                                             (1,100,1000,19,10,100,19),
                                             (1,1000,2000,30,10,25,32),
                                             (2,0,100,6,0,100,10),
                                             (2,100,1000,15,10,100,16),
                                             (2,1000,2000,25,10,20,24),
                                             (3,0,70,6,0,5,3),
                                             (3,70,100,5,0,100,10),
                                             (3,100,1000,9,10,100,12),
                                             (3,1000,2000,10,90,200,11),
                                             (3,2000,3000,10,100,300,10)
    INSERT INTO DISTANCES VALUES  (1,2,10),
                                  (1,3,100),
                                  (1,4,250),
                                  (1,5,300),
								  (1,6,400),
								  (1,7,500),
								  (1,8,550),
								  (1,9,600),
								  (1,10,950),
                                  (2,1,10),
                                  (2,3,200),
                                  (2,4,250),
                                  (2,5,400),
							      (2,6,600),
							      (2,7,750),
								  (2,8,900),
								  (2,9,1000),
								  (2,10,1050),
                                  (3,1,100),
                                  (3,2,200),
                                  (3,4,350),
                                  (3,5,400),
								  (3,6,550),
								  (3,7,700),
								  (3,8,850),
								  (3,9,990),
								  (3,10,1111),
                                  (4,1,250),
                                  (4,2,250),
                                  (4,3,350),
                                  (4,5,400),
								  (4,6,700),
								  (4,7,800),
								  (4,8,900),
								  (4,9,1000),
								  (4,10,1200),
                                  (5,1,300),
                                  (5,2,400),
                                  (5,3,400),
                                  (5,4,400) ,
								  (5,6,500),
								  (5,7,600) ,
								  (5,8,800) ,
								 (5,9,1000),
								  (5,10,1600),
								  (6,1,400),
(6,2,600),
(6,3,550),
(6,4,700),
(6,5,500),
(6,7,600),
(6,8,700),
(6,9,850),
(6,10,1000),
(7,1,500),
(7,2,750),
(7,3,700),
(7,4,800),
(7,5,600),
(7,6,650),
(7,8,700),
(7,9,800),
(7,10,950),
(8,1,550),
(8,2,900),
(8,3,850),
(8,4,900),
(8,5,800),
(8,6,600),
(8,7,700),
(8,9,800),
(8,10,1080),
(9,1,600),
(9,2,1000),
(9,3,950),
(9,4,1000),
(9,5,1100),
(9,6,850),
(9,7,800),
(9,8,800),
(9,10,1500),
(10,1,950),
(10,2,1050),
(10,3,1111),
(10,4,1200),
(10,5,1600),
(10,6,1000),
(10,7,950),
(10,8,1080),
(10,9,1500)  
								    
     INSERT INTO FEEDBACK VALUES('NAM','HO THANH','NAMHTGC60226@FPT.EDU.VN','VERY GOOD')                              
	 INSERT INTO ORDERS VALUES ('A1','2010-04-12',3,1,0,1,5,'2010-04-12','Received','Le Van A','Chau Phu',1,'0975955380','Le Van B','Ha Tien',2,'0975955390','G01',3,'No problem',730,800)
	 Insert into ORDERS (ORDER_NUMBER, ORDER_DATE, SERVICE_ID, ACCOUNT_ID,IS_INTERNAL_DELIVERY,BRANCH_ID_FROM,BRANCH_ID_TO,STATUS,MAIL_CODE,WEIGHT,DESCRIPTION,TOTAL_DELIVERY_FEE,TOTAL_PAID)  values('A2','2010-04-12',2,2,1,3,4,'Sending..','G02',100,'HKT',700,900)                           
	                        
	                          
	                           